package myservice.android.database;

import java.util.Calendar;

import myservice.android.Global;
import myservice.android.R;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {		
	
	public DatabaseHelper(Context context) {
		super(context, Global.databaseName, null, Global.databaseVersion);
	}

	@Override
	public void onCreate(SQLiteDatabase database) {
		Log.i(Global.TAG,"Creating the database...");		

		database.execSQL("CREATE TABLE settings (name TEXT, value TEXT)");
		database.execSQL("INSERT INTO settings VALUES ('databaseversion','"+Global.databaseVersion+"')");		
		database.execSQL("INSERT INTO settings VALUES ('firstdayofweek','0')");
		database.execSQL("INSERT INTO settings VALUES ('showinactives','false')");
		database.execSQL("INSERT INTO settings VALUES ('trackcredittime','false')");
		database.execSQL("INSERT INTO settings VALUES ('autoincrementreturncalls','true')");
		database.execSQL("INSERT INTO settings VALUES ('rounddowntimer','true')");
		database.execSQL("INSERT INTO settings VALUES ('autobackup','true')");	
		database.execSQL("INSERT INTO settings VALUES ('lastbackup','"+Global.getTimeStamp()+"')");	
		database.execSQL("INSERT INTO settings VALUES ('dateformat','0')");			
		database.execSQL("INSERT INTO settings VALUES ('addressformat','0')");		
		database.execSQL("INSERT INTO settings VALUES ('yearrange','0')");				
		database.execSQL("INSERT INTO settings VALUES ('timerstart','0')");
		database.execSQL("INSERT INTO settings VALUES ('smstemplate','"+Global.res.getString(R.string.message_default_sms_template)+"')");	
		database.execSQL("INSERT INTO settings VALUES ('showtotaltimegraph','true')");		
		database.execSQL("INSERT INTO settings VALUES ('showministrytimegraph','false')");		
		database.execSQL("INSERT INTO settings VALUES ('showcredittimegraph','false')");		
		database.execSQL("INSERT INTO settings VALUES ('showmagazinesgraph','true')");		
		database.execSQL("INSERT INTO settings VALUES ('showbrochuresgraph','false')");		
		database.execSQL("INSERT INTO settings VALUES ('showbooksgraph','false')");		
		database.execSQL("INSERT INTO settings VALUES ('showreturncallsgraph','true')");		
		database.execSQL("INSERT INTO settings VALUES ('showbiblestudentsgraph','false')");
		database.execSQL("INSERT INTO settings VALUES ('syncid','"+Global.getRandomSyncId()+"')"); // unique ID used to identify synchronization partners
		database.execSQL("INSERT INTO settings VALUES ('syncexportfolder','')");		
		database.execSQL("INSERT INTO settings VALUES ('syncimportfolder','')");		
		
		Calendar today = Calendar.getInstance();
		database.execSQL("INSERT INTO settings VALUES ('graphsfrommonth','"+today.get(Calendar.MONTH)+"')");		
		database.execSQL("INSERT INTO settings VALUES ('graphstomonth','"+today.get(Calendar.MONTH)+"')");		
		database.execSQL("INSERT INTO settings VALUES ('graphsfromyear','"+(today.get(Calendar.YEAR)-1)+"')");		
		database.execSQL("INSERT INTO settings VALUES ('graphstoyear','"+today.get(Calendar.YEAR)+"')");				
		
		// Interested persons
		database.execSQL("CREATE TABLE persons (personid INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, status INTEGER, comments TEXT, street TEXT, houseapartment TEXT, "+
						 "townregion TEXT, telephone TEXT, timestamp INTEGER)");
		
		// Time spent in ministry and placed literature per day
		database.execSQL("CREATE TABLE ministry (date INTEGER, month INTEGER, year INTEGER, hours INTEGER, minutes INTEGER, chours INTEGER, cminutes INTEGER, "+
		                 "magazines INTEGER, brochures INTEGER, books INTEGER, returncalls INTEGER, comments TEXT, timestamp INTEGER)");
		
		// Calls to persons and territory elements
		database.execSQL("CREATE TABLE calls (callid INTEGER PRIMARY KEY AUTOINCREMENT, date INTEGER, month INTEGER, year INTEGER, "+
						 "personid INTEGER, territoryid INTEGER, comments TEXT, biblestudy INTEGER, timestamp INTEGER)");						
		
		database.execSQL("CREATE TABLE publications (publicationid INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, type INTEGER, timestamp INTEGER)");
		
        database.execSQL("CREATE TABLE placedpublications (publicationid INTEGER, personid INTEGER, placed INTEGER, timestamp INTEGER)");
        
        database.execSQL("CREATE TABLE territories (territoryid INTEGER PRIMARY KEY AUTOINCREMENT, type INTEGER, parent INTEGER, name TEXT, comments TEXT, completed INTEGER, timestamp INTEGER)");
        // type = 0 - territory (can hold 1,2,3), 1 - street (can hold 2,3) , 2 - single-family house, 3 - apartment house (can hold 4,5), 4 - staircase (can hold 5), 5 - apartment        
        
	}

	@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
		Log.i(Global.TAG,"Upgrading the database from version "+oldVersion+" to version "+newVersion+"...");
		
		if (oldVersion == 1) {
			// Add the autoincrement return visits setting
			database.execSQL("INSERT INTO settings VALUES ('autoincrementreturncalls','true')");
			
			// Add the autobackup setting
			database.execSQL("INSERT INTO settings VALUES ('autobackup','true')");
			
			// Set the default last backup date
			database.execSQL("INSERT INTO settings VALUES ('lastbackup','"+Global.getTimeStamp()+"')");	
		}
		
		database.execSQL("UPDATE settings SET value='"+Global.databaseVersion+"' WHERE name='databaseversion'");
		
	}		

}
